from flask import Flask, render_template, request, redirect, url_for, jsonify
import pymysql

app = Flask(__name__)

# 数据库连接参数
db_config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'database': 'test',
    'charset': 'utf8mb4'
}

# 连接数据库
def get_db_connection():
    return pymysql.connect(**db_config)

# 关闭数据库连接
def close_db_connection(conn):
    conn.close()

# 执行SQL语句
def execute_sql(sql_code, params=None):
    conn = get_db_connection()
    try:
        conn.ping(reconnect=True)
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        if params:
            cursor.execute(sql_code, params)
        else:
            cursor.execute(sql_code)
        conn.commit()
        result = cursor.fetchall()
        cursor.close()
        return result
    except pymysql.MySQLError as err_message:
        conn.rollback()
        cursor.close()
        return type(err_message), err_message
    finally:
        close_db_connection(conn)

# 登录界面
@app.route("/")
def index_login():
    return render_template('login.html')

# 注册界面
@app.route("/register")
def index_register():
    return render_template('register.html')

@app.route("/login", methods=["post"])
def login():
    name = request.form.get("username")
    pwd = request.form.get("password")
    sql_code = "SELECT * FROM `login_user` WHERE `username` = %s"
    result = execute_sql(sql_code, (name,))
    if result and len(result) > 0:
        user = result[0]
        if user['password'] == pwd:
            return '密码正确 <a href="/SystemPage.html">点击进去首页</a>'
        else:
            return '密码错误 <a href="/">返回登录</a>'
    else:
        return '用户不存在 <a href="/">返回登录</a>'

@app.route("/register", methods=["post"])
def register():
    name = request.form.get("username")
    pwd = request.form.get("password")
    sql_code = "SELECT * FROM `login_user` WHERE `username` = %s"
    result = execute_sql(sql_code, (name,))
    if result and len(result) > 0:
        return '用户已存在 <a href="/">返回登录</a>'
    else:
        sql_code = "INSERT INTO `login_user` (`username`, `password`) VALUES (%s, %s)"
        execute_sql(sql_code, (name, pwd))
        return '注册成功 <a href="/">返回登录</a>'

# 主界面
@app.route('/SystemPage.html')
def index_SystemPage():
    return render_template('SystemPage.html')

# 回答内容
@app.route('/test.html')
def data_visualization():
    # 获取分页参数
    page = request.args.get('page', 1, type=int)
    per_page = 10  # 每页显示的记录数

    # 查询总记录数
    total_count_sql = "SELECT COUNT(*) AS total FROM answers_1"
    total_count_result = execute_sql(total_count_sql)
    total_count = total_count_result[0]['total']

    # 计算总页数
    total_pages = (total_count + per_page - 1) // per_page

    # 查询当前页的数据
    offset = (page - 1) * per_page
    data_sql = "SELECT AnswerID, UserID, VoteupCount, CommentCount, PublicationTime, Content FROM answers_1 LIMIT %s OFFSET %s"
    answers_list = execute_sql(data_sql, (per_page, offset))

    # 渲染 test.html 并传递数据
    return render_template('test.html', answers=answers_list, current_page=page, total_pages=total_pages)


@app.route('/add_answer', methods=['POST'])
def add_answer():
    data = request.get_json()
    # 根据表结构，这里将原nickname字段去掉，用UserID代表作者昵称
    userId = data.get('UserID')
    voteupCount = data.get('VoteupCount')
    commentCount = data.get('CommentCount')
    publicationTime = data.get('PublicationTime')
    content = data.get('Content')

    # 去掉手动获取最大AnswerID并自增的逻辑，因为数据库会自动处理自增
    sql_code = """
    INSERT INTO answers_1 (UserID, VoteupCount, CommentCount, PublicationTime, Content)
    VALUES (%s, %s, %s, %s, %s)
    """
    params = (userId, voteupCount, commentCount, publicationTime, content)
    try:
        execute_sql(sql_code, params)
        return jsonify({'success': True})
    except Exception as e:
        print(e)
        return jsonify({'success': False, 'message': str(e)})

@app.route('/delete_answer', methods=['POST'])
def delete_answer():
    data = request.get_json()
    answerId = data.get('AnswerID')
    sql_code = "DELETE FROM answers_1 WHERE AnswerID = %s"
    params = (answerId,)
    try:
        execute_sql(sql_code, params)
        return jsonify({'success': True})
    except Exception as e:
        print(e)
        return jsonify({'success': False, 'message': str(e)})

@app.route('/update_answer', methods=['POST'])
def update_answer():
    data = request.get_json()
    answerId = data.get('AnswerID')
    userId = data.get('UserID')
    voteupCount = data.get('VoteupCount')
    commentCount = data.get('CommentCount')
    publicationTime = data.get('PublicationTime')
    content = data.get('Content')

    sql_code = """
    UPDATE answers_1 
    SET UserID = %s, 
        VoteupCount = %s, 
        CommentCount = %s, 
        PublicationTime = %s, 
        Content = %s 
    WHERE AnswerID = %s
    """
    params = (userId, voteupCount, commentCount, publicationTime, content, answerId)

    try:
        execute_sql(sql_code, params)
        return jsonify({'success': True})
    except Exception as e:
        print(e)
        return jsonify({'success': False, 'message': str(e)})

# 问题内容
@app.route('/Question.html')
def question_management():
    page = request.args.get('page', 1, type=int)
    per_page = 10
    total_count_sql = "SELECT COUNT(*) AS total FROM questions"
    total_count_result = execute_sql(total_count_sql)
    total_count = total_count_result[0]['total']
    total_pages = (total_count + per_page - 1) // per_page
    offset = (page - 1) * per_page
    data_sql = "SELECT QuestionID, Title, CreationTime, FollowerCount, VisitCount, Tags, URLQuestionID FROM questions LIMIT %s OFFSET %s"
    questions_list = execute_sql(data_sql, (per_page, offset))
    return render_template('Question.html', questions=questions_list, current_page=page, total_pages=total_pages)

@app.route('/add_question', methods=['POST'])
def add_question():
    data = request.get_json()
    title = data.get('title')
    creationTime = data.get('creationTime')
    followerCount = data.get('followerCount')
    visitCount = data.get('visitCount')
    tags = data.get('tags')
    urlQuestionID = data.get('urlQuestionID')
    sql_code = "INSERT INTO questions (Title, CreationTime, FollowerCount, VisitCount, Tags, URLQuestionID) VALUES (%s, %s, %s, %s, %s, %s)"
    params = (title, creationTime, followerCount, visitCount, tags, urlQuestionID)
    try:
        execute_sql(sql_code, params)
        return jsonify({'success': True})
    except Exception as e:
        print(e)
        return jsonify({'success': False, 'message': str(e)})

@app.route('/delete_question', methods=['POST'])
def delete_question():
    data = request.get_json()
    questionId = data.get('questionId')
    sql_code = "DELETE FROM questions WHERE QuestionID = %s"
    params = (questionId,)
    try:
        execute_sql(sql_code, params)
        return jsonify({'success': True})
    except Exception as e:
        print(e)
        return jsonify({'success': False, 'message': str(e)})

@app.route('/update_question', methods=['POST'])
def update_question():
    data = request.get_json()
    questionId = data.get('questionId')
    title = data.get('title')
    creationTime = data.get('creationTime')
    followerCount = data.get('followerCount')
    visitCount = data.get('visitCount')
    tags = data.get('tags')
    urlQuestionID = data.get('urlQuestionID')
    sql_code = "UPDATE questions SET Title = %s, CreationTime = %s, FollowerCount = %s, VisitCount = %s, Tags = %s, URLQuestionID = %s WHERE QuestionID = %s"
    params = (title, creationTime, followerCount, visitCount, tags, urlQuestionID, questionId)
    try:
        execute_sql(sql_code, params)
        return jsonify({'success': True})
    except Exception as e:
        print(e)
        return jsonify({'success': False, 'message': str(e)})

# 提问者个人信息（未完成）
@app.route('/User.html')
def index_User():
    return render_template('User.html')


# 点赞分析图（未完成）
@app.route('/AnalysisChart.html')
def index_AnalysisChart():
    return render_template('AnalysisChart.html')

# 评论分析图（未完成）
@app.route('/CommentChart.html')
def index_CommentChart():
    return render_template('CommentChart.html')

# 其他待补充

if __name__ == '__main__':
    app.run(debug=True)